--
-- Incorporacion del los dias de antiguedad de deuda permitidos en la funcion de limite_rapido
--

DROP FUNCTION limite_credito.get_limite_rapido(integer, character varying, character varying);

CREATE OR REPLACE FUNCTION limite_credito.get_limite_rapido(
	pidpad integer,
	sys_unisuc character varying,
	pcodpre character varying)
    RETURNS TABLE(limite numeric, diasdeuda integer, saldo_ctacte numeric, saldo_valores numeric, saldo_stock numeric, dias_atraso integer) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 100
AS $BODY$
DECLARE
BEGIN

RETURN QUERY EXECUTE '
WITH GRUPOS 
AS (SELECT PAD.ID AS IDPAD, PADLIMGRU.COD, CASE WHEN PARTICULAR.IDPAD IS NULL THEN GRUPAL.LIMITE ELSE PARTICULAR.LIMITE END LIMITE,
CASE WHEN PARTICULAR.IDPAD IS NULL THEN GRUPAL.DIASDEUDA ELSE PARTICULAR.DIASDEUDA END DIASDEUDA
FROM PAD
LEFT JOIN PADLIM GRUPAL     ON PAD.TIPOPADA = GRUPAL.TIPOPADA AND (PAD.CODPADR = GRUPAL.CODPADR OR PAD.CODPADR ILIKE GRUPAL.CODPADR || ''.%'')
LEFT JOIN PADLIM PARTICULAR ON PAD.ID = PARTICULAR.IDPAD
LEFT JOIN PADLIMGRU 		ON GRUPAL.CODPADLIMGRU = PADLIMGRU.COD OR PARTICULAR.CODPADLIMGRU = PADLIMGRU.COD
)

SELECT CAST(MIN(LIMITE) AS NUMERIC) AS LIMITE,
	   CAST(coalesce(min(DiasDeuda),0) AS INTEGER) AS DiasDeuda,
       CAST(COALESCE(SUM(Saldo_CtaCte),0) AS NUMERIC)  as Saldo_CtaCte, 
	   CAST(COALESCE(Sum(Saldo_Valores),0) AS NUMERIC) as Saldo_Valores, 
	   CAST(COALESCE(sum(Saldo_Stock),0) AS NUMERIC) as Saldo_Stock,
	   CAST(COALESCE(max(Dias_Atraso),0) AS INTEGER) AS Dias_Atraso
FROM
(SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA, SUM(CTEM.IMPOMONPEND * SIGNO) AS Saldo_CtaCte, 0.00 as Saldo_valores, 0.00 as Saldo_Stock, 
 cast(now() as date) - coalesce(CAST(MIN(ctem.VTO) AS DATE), cast(now() as Date)) as Dias_Atraso
FROM GRUPOS
LEFT JOIN PADLIMVAL ON PADLIMVAL.CODPADLIMGRU = GRUPOS.COD
INNER JOIN CTEM ON CTEM.TIPOVALT = PADLIMVAL.TIPOVALT AND CTEM.IDPAD = GRUPOS.IDPAD
INNER JOIN COMM ON COMM.ID = CTEM.IDCOMM
WHERE (CTEM.IMPOMONPEND > 0 OR (CTEM.IDVAL IS NOT NULL AND COMM.IDPAD = GRUPOS.IDPAD)) 
AND GRUPOS.IDPAD = ' || pIdPad || 
' AND  (INDEX(''' || SYS_UniSuc || ''', COMM.codambsuc) > 0 )
GROUP BY GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA
UNION
SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA, 0, SUM(CTEM.IMPO * SIGNO) AS Saldo_Valores, 0,0
FROM GRUPOS
LEFT JOIN PADLIMVAL ON PADLIMVAL.CODPADLIMGRU = GRUPOS.COD
INNER JOIN CTEM ON CTEM.TIPOVALT = PADLIMVAL.TIPOVALT
INNER JOIN COMM ON COMM.ID = CTEM.IDCOMM
INNER JOIN VAL  ON VAL.ID = CTEM.IDVAL
INNER JOIN PAD CAJA ON VAL.IDPADQUELOTIENE = CAJA.ID AND CAJA.CODPADR ILIKE ''F.CAJ.CAJ%''
WHERE (COMM.IDPAD = GRUPOS.IDPAD) 
AND GRUPOS.IDPAD = ' || pIdPad || 
' AND  (INDEX(''' || SYS_UniSuc || ''', COMM.codambsuc) > 0 )
GROUP BY GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA
UNION
SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA, 0, 0, SUM(PRE_OBTENERPRECIO(''' || pCodPre || ''',ARTM.IDART,NULL,NULL) * CANTPENDIENTE * 
CASE WHEN PADLIMEST.SIGNO = ''+'' THEN 1 ELSE -1 END) AS Saldo_Remitos, 0
FROM GRUPOS
INNER JOIN PADLIMEST ON PADLIMEST.CODPADLIMGRU = GRUPOS.COD
INNER JOIN ARTM ON ARTM.CODSTKEST = PADLIMEST.CODSTKEST
INNER JOIN COMM ON COMM.ID = ARTM.IDCOMM AND COMM.IDPAD = GRUPOS.IDPAD
WHERE GRUPOS.IDPAD = ' || pIdPad || 
' AND  (INDEX(''' || SYS_UniSuc || ''', COMM.codambsuc) > 0 )
AND ARTM.CantPendiente > 0
GROUP BY GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA
UNION
SELECT GRUPOS.IDPAD, GRUPOS.LIMITE, GRUPOS.DIASDEUDA, 0, 0, 0, 0
FROM GRUPOS
WHERE GRUPOS.IDPAD = ' || pIdPad || ') Pendientes
GROUP BY idpad';

END;
$BODY$;

ALTER FUNCTION limite_credito.get_limite_rapido(integer, character varying, character varying)
    OWNER TO abacouser;
    
--	
SELECT aaversion('494.01','494.00');
--
